The CICA Windows Explosion!

home *** CD-ROM | disk | FTP | other *** search

/ The CICA Windows Explosion! / The CICA Windows Explosion! - Disc 2.iso / excel / we0146.exe / WE0146T.TXT

Wrap

Text File | 1991-10-18 | 28KB | 678 lines

====================================================================== Microsoft Product Support Services Application Note (Text File) WE0146: MOST FREQUENTLY ASKED QUESTIONS ====================================================================== Revision Date:10/91 No Disk Included The following information applies to Microsoft Excel for Windows version 3.0 -------------------------------------------------------------------- | INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY | | ACCOMPANY THIS DOCUMENT (collectively referred to as an | | Application Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY | | KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO | | THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A | | PARTICULAR PURPOSE. The user assumes the entire risk as to the | | accuracy and the use of this Application Note. This Application | | Note may be copied and distributed subject to the following | | conditions: 1) All text must be copied without modification and | | all pages must be included; 2) If software is included, all files | | on the disk(s) must be copied without modification [the MS-DOS(R) | | utility DISKCOPY is appropriate for this purpose]; 3) All | | components of this Application Note must be distributed together; | | and 4) This Application Note may not be distributed for profit. | | | | Copyright 1991 Microsoft Corporation. All Rights Reserved. | | Microsoft, MS-DOS, and the Microsoft logo are registered | | trademarks and Windows is a trademark of Microsoft Corporation. | -------------------------------------------------------------------- IMPORTING/EXPORTING TEXT FILES ============================== ---------------------------------------------------------------------- QUESTION: How can I import text files from a third-party application into Excel? For example, I have some text files that I have downloaded from my company's mainframe system, and I would like to bring these into Excel 3.0. How can I do this? ANSWER: If you are importing a text file from a third-party application into Excel, it is usually necessary to convert the file into a format that Excel can use. This involves parsing the data so that each field in each row appears in its own cell on the Excel worksheet. Each field in each record must be separated from the next by a "delimiter" -- a character that marks the end of one unit of data and the beginning of another. Excel uses this delimiter to parse the data. There are three different ways to bring text files into Excel, depending on the type of delimiter used in the text file: 1. If the file has a column delimiter that is either a tab or a comma, the file can be opened by choosing Open from the File menu. From the File Open dialog box, select the filename from the list of files, or type in the filename, and choose the Text button. From the Column Delimiter box, choose Tab or Comma, as appropriate for your file, and choose OK. 2. Another quick way to import a tab- or comma-delimited file into Excel is to name the file with a .TXT extension (if tab delimited) or a .CSV extension (if comma delimited). Excel will recognize the format from the file extension and open it accordingly. 3. If the file has a column delimiter other than a tab or a comma, the file can be brought into Excel as a text file (it will open this way if opened normally in Excel) and then parsed using the Excel 3.0 add-in macro FLATFILE.XLA. To use FLATFILE.XLA, do the following: a. From the File menu, choose Open. Select FLATFILE.XLA from the LIBRARY directory located in your Excel directory. b. Open your text file. Highlight column A, and from the Data menu, choose Smart Parse (the Smart Parse command will be available only if you have opened FLATFILE.XLA). c. If columns in your text file are delimited by a space or spaces, choose the Blank Space ( ) option. If the delimiter is a forward slash, choose the Slash (/) option. If the delimiter is some other character, choose Other and type in the character. If the file is a flat file (a file that uses spaces between the columns to delimit the file), there may be extra spaces between the fields in the file. If you would like to remove the spaces, select Remove Extra Blank Spaces. When you choose OK, each field in each row will be parsed out to its own cell. For more information on opening files in Excel, see pages 338-340 of the "Microsoft Excel User's Guide." For more information on the FLATFILE.XLA add-in macro, see pages 683- 684 in Appendix B of the "Microsoft Excel User's Guide." ---------------------------------------------------------------------- QUESTION: I created a worksheet in Excel 3.0 and now need to export this file from Excel to my mainframe. How can I do this? ANSWER: Just as Excel can read in text files, it can also save worksheet files in text format. If you prefer a tab- or comma- delimited file, save the file by choosing Save As from the File menu; choose Options, and from the File Format box, select either Text format for a tab-delimited file or CSV for a comma-delimited file. If you want to save the file as a flat file (a file that uses spaces between the columns to delimit the file), you can use the FLATFILE.XLA add-in macro: 1. From the File menu, choose Open. Select FLATFILE.XLA from the LIBRARY directory located in your Excel directory. 2. Highlight the area of the worksheet that you want to save to a flat file format. 3. From the Data menu, choose Export (this command will only be available if FLATFILE.XLA is open). The add-in macro will prompt you for a filename. Type in a filename and choose Export. This file can now be imported into any application that requires an ASCII flat file. For more information on saving files from Excel, see pages 135-143 of the "Microsoft Excel User's Guide." For more information on the FLATFILE.XLA add-in macro, see pages 683- 684 in Appendix B of the "Microsoft Excel User's Guide." TRANSFERRING DATA FROM AN EXCEL MACRO ===================================== ---------------------------------------------------------------------- QUESTION: I have written an Excel macro that prompts the user for input and manipulates the information. However, the information is not being returned to the worksheet. What am I doing wrong? ANSWER: One of the common misconceptions about Excel macros is the way that data is transferred to a spreadsheet. Each function in a macro returns a value to the cell in which the formula was entered. The result of that function is not placed in the worksheet without using a function to specifically do so. The FORMULA function is the function that should be used for entering data into a cell from a command macro. The FORMULA function is one of the most important macro functions. It is the equivalent of typing data into a cell and pressing enter. The syntax of the function is: FORMULA(<formula_text>,<reference>) The content, <formula_text>, whether it be a value or a reference to a value, will be placed in <reference>. If <reference> is omitted, the value will be placed in the active cell. The following example uses the FORMULA function to enter the value returned from an INPUT function into cell A1 of SHEET1.XLS. The INPUT function prompts the user to enter a number. A 1 Example of FORMULA() 2 =INPUT("Please Enter a Number",1) 3 =FORMULA(A2,SHEET1.XLS!$A$1) 4 =RETURN() If we assume that the user entered the number 987654321, cell A1 of SHEET1.XLS will contain that number after the macro is run. For more information on the FORMULA function, see pages 89-90 of the "Microsoft Excel Function Reference." USING OFFSET TO REFERENCE CELLS IN AN EXCEL MACRO ================================================= ---------------------------------------------------------------------- QUESTION: I am trying to speed up the execution of my macros, and I understand that moving the active cell takes up time unnecessarily. How can I refer to cells within a macro without moving the active cell? Generally, I select another cell that is a certain number of cells to the right or below the active cell. ANSWER: The OFFSET function provides the functionality that you have described. The OFFSET function returns the reference of a cell or the contents of that cell offset a specified number of rows and columns from a reference. This allows you to obtain or retrieve information from a cell without selecting it first. The syntax of the function is: OFFSET(<reference>,<rows>,<cols>,<height>,<width>) The function returns a reference of a specified <height> and <width>, offset from another <reference> by a specified number of <rows> and <columns>. The following examples both place the numbers 1 through 100 into cells A1:A100 on the active sheet. The first macro moves the active cell using the SELECT function. The second macro does not move the active cell and is thus faster. A B 1 First Macro Second Macro 2 =SELECT(!A1) =FOR("i",1,100) 3 =FOR("i",1,100) =FORMULA(i,OFFSET(!A1,i-1,0)) 4 =FORMULA(i,ACTIVE.CELL()) =NEXT 5 =SELECT("R[1]C") =RETURN() 6 =NEXT() 7 =RETURN() For more information on the OFFSET function, refer to pages 163-164 of the "Microsoft Excel Function Reference." USING SELECT TO DUPLICATE COMMON KEYSTROKE COMMANDS IN AN EXCEL MACRO ===================================================================== ---------------------------------------------------------------------- QUESTION: I want to record a macro that contains the keystroke SHIFT+CTRL+DOWN ARROW, which selects everything from the position of the active cell to the bottom of the column of data where the active cell is at the time. However, what the recorder actually records is the fact that I selected, say, cells A1 through A12, so that even if the size of my column of data changes, the size and position of the selection do not. What am I doing wrong? ANSWER: The Macro Recorder in Excel does record SHIFT+CTRL+<arrow key> as a static SELECT statement. These SELECT statements will not have the same effect as the keystrokes when applied to blocks of data that may move or vary in size. The only workaround is to edit the macro to contain code that duplicates the effect of the keystroke. The following macro duplicates SHIFT+CTRL+DOWN ARROW: A 1 Select_Macro 2 =SET.NAME("Top_Cell",ACTIVE.CELL()) 3 =SELECT.END(4) 4 =SELECT(Top_Cell:ACTIVE.CELL(),Top_Cell) 5 =RETURN() The codes to perform SHIFT+CTRL+UP ARROW, LEFT ARROW, or RIGHT ARROW operations are substantially similar; the only thing that must be changed is the number in the SELECT.END statement. The following are the numbers that can be used with SELECT.END: Parameter Direction --------- --------- 1 Left 2 Right 3 Up 4 Down The following macro code duplicates SHIFT+CTRL+END: A 1 Select_Macro 2 =SET.NAME("Top_Cell",ACTIVE.CELL()) 3 =SELECT.LAST.CELL() 4 =SELECT(Top_Cell:ACTIVE.CELL()Top_Cell) 5 =RETURN() The following macro duplicates SHIFT+CTRL+HOME: A 1 Select_Macro 2 =SELECT(TEXTREF("!R1C1"):ACTIVE.CELL(),ACTIVE.CELL()) 3 =RETURN() For more information, see the SELECT statement section on pages 209- 213 of the "Microsoft Excel Function Reference." USING SUM AND IF TO CREATE CONDITIONAL FORMULAS =============================================== ---------------------------------------------------------------------- QUESTION: I would like to count the number of times that a particular entry occurs in a range of cells. Is there a formula for doing this? ANSWER: By using the SUM function and a conditional statement entered as an array, you can count all occurrences of a given value or text string. For example, how many times does the string "abc" occur in cells A1:B5? A B C 1 abc 123 {=SUM((A1:B5="abc")*1)} 2 xyz 456 3 777 aaa 4 987 abc 5 abc hello The formula in cell C1 is an array formula so it is typed in as it appears above WITHOUT the braces ({}), and then entered into the cell by pressing CTRL+SHIFT+ENTER. The result of this formula is 3. ---------------------------------------------------------------------- QUESTION: I would like to evaluate all the cells in a range on my worksheet, and if the value of the cell is 4, I would like to add 4 to the total in the cell. Is there a formula that can do this? ANSWER: Using the SUM function, you can create a formula to total all the cells in a range that contain the number 4. A B C D 1 abc 123 111 {=SUM((A1:C4=4)*4)} 2 xyz 456 4 3 777 4 4 4 4 abc hello The formula in cell D1 is an array formula so it is typed in as it appears above WITHOUT the braces ({}), and then entered into the cell by pressing CTRL+SHIFT+ENTER. The result of this formula is 16. ---------------------------------------------------------------------- QUESTION: I have two columns of data. The first column consists of numbers ranging from 1 to 3. The second column contains values for each entry in the first column. I would like to create a formula that will total all the entries in the second column where a "1" appears in the first column. ANSWER: Assuming the following data, you can use the SUM and IF functions to total all the entries in column B where a "1" appears in column A. A B C 1 1 10 {=SUM(IF(A1:A6=1,B1:B6,0))} 2 2 20 3 1 30 4 1 40 5 3 50 6 1 60 The formula in cell C1 is an array formula so it is typed in as it appears above WITHOUT the braces ({}), and then entered into the cell by pressing CTRL+SHIFT+ENTER. The result of this formula is 140. Note: In the above example, the ranges must be the same length, or you may get an #N/A error. LOOPING STRUCTURES IN EXCEL MACROS ================================== ---------------------------------------------------------------------- QUESTION: When writing an Excel macro, because I execute the same commands over and over, I find that the macro is getting rather long. Is there a way that I can streamline the macro? ANSWER: Excel has several functions that enable you to create looping structures similar to those found in many programming languages. Loops enable a macro to repeat a set of commands a number of times. Excel has three looping functions: FOR, FOR.CELL, and WHILE. The FOR loop is used when you want to execute a set of commands a fixed number of times that is determined prior to entering the loop. FOR(<counter_text>,<start_num>,<end_num>,<step_num>) For example, the following FOR-NEXT loop will execute five times and will shade every fifth cell in a column, starting with the currently selected cell: A 1 For_Next_Macro 2 =FOR("counter",1,5,1) 3 =BORDER(FALSE,FALSE,FALSE,FALSE,FALSE,TRUE) 4 =SELECT("R(5)C") 5 =NEXT() 6 =RETURN() The FOR.CELL loop is used when you want to perform a set of commands on every cell in a specified range. FOR.CELL(<ref_name>,<area_ref>,<skip_blanks>) The <ref_name> argument can be used within the loop to refer to the current cell being evaluated in the FOR.CELL loop. If a reference is not specified in the second argument, <area_ref>, the FOR.CELL loop will be executed on the currently selected range of cells. The following FOR.CELL-NEXT loop will multiply each value in cells A1:D10 by 100 (cells A1:D10 are located on the active worksheet): A 1 For.Cell_Next_Macro 2 =FOR.CELL("current",!A1:!D10,TRUE) 3 =FORMULA(current*100,current) 4 =NEXT() 5 =RETURN() The WHILE looping function will perform a set of commands as long as a certain condition is met. WHILE(<logical_test>) Each time the WHILE function executes, it will evaluate the logical test to see if it is TRUE. If it is TRUE, the commands in the loop will execute. If it is FALSE, the macro will drop out of the WHILE loop. In the following example, the WHILE loop will continue to execute down a column of cells until the active cell is blank. This method is useful if there is an undetermined number of entries in a row or column and you would like the WHILE loop to continue until there are no more entries. If the active cell contains the letter "A", the entry will be replaced with the word "Excellent". A 1 While_Next_Macro 2 =WHILE(NOT(ISBLANK(ACTIVE.CELL()))) 3 =IF(ACTIVE.CELL()="A",FORMULA("Excellent"ACTIVE.CELL())) 4 =SELECT("R(1)C") 5 =NEXT() 6 =RETURN() If you review the preceding examples, you will notice that each looping structure ends with a NEXT function. The NEXT function tells Excel where to return to the previous FOR, FOR.CELL, or WHILE statement. The NEXT function is required when using any of these statements. You can exit from the middle of any loop by using the BREAK function. If speed is a consideration, a FOR loop or a FOR.CELL loop should be used in place of a WHILE loop whenever possible. The WHILE loop tends to be slightly slower than the other two because Excel must evaluate the conditional statement each time it executes. For examples and more in-depth coverage of each of the looping functions, see pages 602-605 of the "Microsoft Excel User's Guide." CREATING DDE LINKS BETWEEN EXCEL AND WORD FOR WINDOWS ===================================================== ---------------------------------------------------------------------- QUESTION: I have a chart on my Excel worksheet, and I want to paste link the chart into Word for Windows so that if I change the chart, the picture of it in Word for Windows changes as well. How can I do this? ANSWER: To paste link the embedded chart into Word for Windows, do the following: 1. Double-click the chart to open it in its own window. 2. From the Chart menu, choose Select Chart. 3. Hold down the SHIFT key and choose Copy Picture from the Edit menu. Select the desired options. 4. Activate the Word for Windows document, and choose Paste Link from the Edit menu. If the chart has its own chart file, follow steps 2 through 4 above. For more information on linking documents from Microsoft Excel into other applications, see pages 316-319 of the "Microsoft Excel User's Guide." USING EXCEL'S STATISTICAL FUNCTIONS =================================== ---------------------------------------------------------------------- QUESTION: How can I return the additional regression statistics that are built into the LINEST function? ANSWER: The Excel 3.0 LINEST function returns several additional statistics that were not available with earlier versions of Excel. To retrieve these statistics, you must first select an appropriately sized array on your worksheet. The array should be five rows high and two columns wide. If your original data includes more than one x- variable, your array should include one extra column for each additional x-variable. For example, if you have three x-variables, your array will be five rows by four columns. The syntax of the LINEST function is: =LINEST(<known_y's>,<known_x's>,<const>,<stats>) By setting the <stats> argument to TRUE, you instruct Excel to return the additional statistics. If <stats> is set to FALSE, the LINEST function will return only the slope and the y-intercept. (See pages 138-141 of the "Microsoft Excel Function Reference" for a complete discussion of the <known_y's>, <known_x's>, and <const> arguments.) Example ------- A B C 1 Known-Y's XVar1 XVar2 2 200 15 76 3 210 20 65 4 195 23 66 5 235 28 72 6 250 36 80 The data in the example shown above includes two x-variables, so the array that must be selected to return the LINEST statistics will be five rows by three columns. After typing in the formula, you must press CTRL+SHIFT+ENTER. This enters the formula as an array formula, and you will see braces ({}) placed around the formula in the formula bar. The additional statistics are returned in the last three rows and first two columns of your array. If your array has more than the required number of columns (two), #N/A errors will be returned in the extra columns. Select cells E2:G6 and enter the following: =LINEST(A2:A6,B2:C6,,TRUE) For this example, omit the third argument. The results are returned as follows: E F G 2 1.017709 2.22756 90.57607 3 1.167926 0.934885 76.37852 4 0.840776 13.3242 #N/A 5 5.280472 2 #N/A 6 1874.931 355.0688 #N/A Cells E3:F6 contain the additional regression statistics. ---------------------------------------------------------------------- QUESTION: How can I retrieve the correlation coefficient and/or the coefficient of determination? ANSWER: The Excel 3.0 LINEST function automatically returns the coefficient of determination (r-squared). The correlation coefficient is the square root (r) of this value. To retrieve the coefficient of determination, use the method shown in the above example. The r-squared value is found in the third row, first column (cell E4) of the resulting array. To find the correlation coefficient, take the square root of this value. To retrieve these values without returning all the other statistics, use the following formulas: Coefficient of Determination: =INDEX(LINEST(<known_y's>,<known_x's>,<const>,TRUE),3,1) Using the example above, this is: =INDEX(LINEST(A2:A6,B2:C6,,TRUE),3,1) Correlation Coefficient: =SQRT(INDEX(LINEST(<known_y's>,<known_x's>,<const>,TRUE),3,1)) Using the example above, this is: =SQRT(INDEX(LINEST(A2:A6,B2:C6,,TRUE),3,1)) Be sure to enter these formulas as array formulas by pressing CTRL+SHIFT+ENTER. For more information on Excel's LINEST function, see pages 138-141 of the "Microsoft Excel Function Reference." AUTO_OPEN MACROS ================ ---------------------------------------------------------------------- QUESTION: How do I set up a macro so that it runs every time I open a document? ANSWER: To have a macro run automatically every time a document is opened, do the following: 1. Open the document (this can be either a worksheet or a macro sheet). 2. From the Formula menu, choose Define Name. 3. In the Name box, type a name that starts with Auto_Open (for example, Auto_Open_1, Auto_Open_Menu). 4. In the Refers To box, type the name or reference of the macro you want to run. If you enter an external reference, Excel opens the macro sheet (if it is not already open) before running the macro. Example ------- The following steps will allow the macro defined as TEST on MACRO1.XLM to automatically run every time SHEET1.XLS is opened: 1. Make sure TEST is working properly before defining it to run automatically. 2. Activate SHEET1.XLS and choose Define Name from the Formula menu. In the Name box, type "Auto_Open" (without the quotation marks). 3. In the Refers To box, type "MACRO1.XLM!TEST" (without the quotation marks). 4. Choose the OK button and save SHEET1.XLS. The next time SHEET1.XLS is opened, MACRO1.XLM will load and the macro TEST will run. For more information, see page 622 of the "Microsoft Excel User's Guide." EDITING AN EXCEL CHART SERIES ============================= ---------------------------------------------------------------------- QUESTION: How can I update my chart when I add additional data to the spreadsheet without re-creating the chart? ANSWER: To update the chart when you add additional data to a series, you will need to modify the chart Series formula. There are two methods to accomplish this: Method 1 -------- 1. Activate the chart you want to update. 2. From the Chart menu, choose Edit Series. 3. From the Series box, select the name of the series you want to update. 4. Update the references in the X Labels and Y Values boxes to include the new data points that were added to the worksheet. Method 2 -------- 1. Activate the chart you want to update. 2. Select the series you want to update. 3. Edit the series formula in the formula bar to reflect the new data points on the worksheet. If you want to add an additional series to the chart, rather than modify an existing one, do one of the following: Method 1 -------- 1. Activate the chart you want to update. 2. From the Chart menu, choose Edit Series. 3. From the Series box, select New Series. 4. Update the references in the X Labels and Y Values boxes to include the new column or row of data that defines your new series. Method 2 -------- 1. Highlight the data for the new series on the worksheet. 2. From the Edit menu, choose Copy. 3. Activate the chart you want to update. 4. From the Edit menu, choose Paste. For more information on editing a Series formula, see page 422 of the "Microsoft Excel User's Guide." Note: In all the methods described above, defined names for the cell ranges may be substituted for the actual cell references. This option enables the chart to be updated by redefining the range name on the worksheet to include the new data. For more information on naming a cell or range of cells on a worksheet, see page 224 of the "Microsoft Excel User's Guide."